package com.threeti.mecool.core.application.basicdata;

import com.threeti.mecool.core.domain.model.basicdata.biz.Channel;
import com.threeti.mecool.core.domain.model.basicdata.biz.ChannelSync;
import com.threeti.mecool.core.infrastructure.persistence.basicdata.biz.ChannelSyncRepository;
import com.threeti.mecool.framework.util.Lists;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import java.util.*;

/**
 * 基础数据同步服务
 *
 * @author Jay Meng
 */
@Service
public class BasicDataSyncService {
    public static final Logger logger = LoggerFactory.getLogger(BasicDataSyncService.class);

    @Autowired
    private BasicDataService basicService;

    @Autowired
    private ChannelSyncRepository channelSyncRepo;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @PersistenceContext
    EntityManager em;

    @Transactional
    public void initializeChannelSync() {
        long countOfSlave = jdbcTemplate.queryForLong("select count(*) from DM_CHANNEL_SYNC");
        if (countOfSlave != 0) {
            throw new RuntimeException("无法初始化同步通路数据[表DM_CHANNEL_SYNC],必须先清空期内的数据");
        }
        //创建虚拟终端，通常这些终端不关心GPS位置，比如在制定计划时，某些日常任务可在明酷公司本部(该位置即可视为一个虚拟终端)执行，这些任务不关心GPS，无需记录在相关报表中//->
        ChannelSync channelWithoutGPS = new ChannelSync(new Channel("-1", "其它终端", null, null, null, null), ChannelSync.TYPE_NOT_CONCERNED_ABOUT_GPS);
        channelSyncRepo.saveAndFlush(channelWithoutGPS);
        //<-

        List<Map<String, Object>> allTheChannelCodes = jdbcTemplate.queryForList("select POS_CODE from MD_POS");
        List<String> channelCodes = Lists.newArrayList();
        for (Map<String, Object> map : allTheChannelCodes) {
            Object channelCode = map.get("POS_CODE");
            if (channelCode != null) channelCodes.add((String) channelCode);
        }

        for (String channelCode : channelCodes) {
            Channel channel = basicService.preProcessChannelBy(channelCode);
            ChannelSync channelSync = new ChannelSync(channel);
            channelSyncRepo.saveAndFlush(channelSync);
        }
    }

    /**
     * 本同步方法假设通路编码是不变的，并且只能感知到新增或更新过的通路数据，无法感知到删除的数据
     */
    @Transactional
    public void doSyncOfChannelSync() {
        final String countSql = "select count(*) from (select 0 from dm_channel_sync group by channel_code having count(*)>1)";
        final String clearSql = "delete from DM_CHANNEL_SYNC where id not in (select channel from dm_project_store union all select channel from DM_WF_PLAN_JOB_RESULT)";
        long count = jdbcTemplate.queryForLong(countSql);
        if (count > 0) {
            jdbcTemplate.execute(clearSql);
        }

        //TODO jay:按照业务场景,检测条件可能还要增加->
        List<Map<String, Object>> posCodesNeedToBeUpdated = jdbcTemplate.queryForList(
                "select master.POS_CODE from MD_POS master inner join DM_CHANNEL_SYNC slave on master.POS_CODE = slave.CHANNEL_CODE " +
                        "where master.AD_ID != slave.CHANNEL_ADMIN_DIV_ID or master.POS_NAME != slave.CHANNEL_NAME or master.POS_ADDR != slave.CHANNEL_ADDRESS or master.POS_KIND_CODE != slave.CHANNEL_CAT_CODE or master.POS_SYS_KIND != slave.CHANNEL_SYS_CAT");
        //<-

        //do sync of datas existed in both master and slave
        logger.debug("do sync of datas existed in both master[MD_POS] and slave[DM_CHANNEL_SYNC] START...");
        for (Map<String, Object> map : posCodesNeedToBeUpdated) {
            logger.debug("sync existed datas...", map);
            Object channelCodeObj = map.get("POS_CODE");
            String channelCode = (String) channelCodeObj;
            Channel channel = basicService.preProcessChannelBy(channelCode);
            ChannelSync channelSync = channelSyncRepo.findByChannelCode(channelCode);
            channelSync.setChannel(channel);
            channelSyncRepo.saveAndFlush(channelSync);
        }
        logger.debug("do sync of datas existed in both master[MD_POS] and slave[DM_CHANNEL_SYNC] END...");
        //<-

        //do sync of new datas found in master
        logger.debug("do sync of new datas found in master[MD_POS] START...");
        long countOfMaster = jdbcTemplate.queryForLong("select count(*) from MD_POS");
        long countOfSlave = jdbcTemplate.queryForLong("select count(*) from DM_CHANNEL_SYNC");
        if (countOfMaster != countOfSlave) {
            List<Map<String, Object>> posCodesNeedToBeCreated = jdbcTemplate.queryForList(
                    "select POS_CODE from MD_POS master where not exists (select CHANNEL_CODE from DM_CHANNEL_SYNC where CHANNEL_CODE = master.POS_CODE)");
            for (Map<String, Object> map : posCodesNeedToBeCreated) {
                logger.debug("sync new data => {}", map);
                Object channelCodeObj = map.get("POS_CODE");
                String channelCode = (String) channelCodeObj;
                Channel channel = basicService.preProcessChannelBy(channelCode);
                channelSyncRepo.saveAndFlush(new ChannelSync(channel));
            }
        }
        logger.debug("do sync of new data found in master[MD_POS] END...");
    }

    @SuppressWarnings("unchecked")
    @Transactional
    public void synchronizeChannelSyncWithPos() {
        List<_AdminDiv> adminDiv_list;
        Map<Integer, _AdminDiv> id_adminDiv_map;
        Map<Integer, _AdminDiv> code_adminDiv_map;
        List<_Pos> pos_list;
        Map<String, Long> code_id_channel_map;
        List<_ChannelSync> channelSync_list;
        String s;
        List<Object[]> ds;
        //<editor-fold desc="admin div">
        s = "select ad_id, ad_code, ad_level, deep, ad_name, city_ad_name, province_ad_name from md_admin_div where is_del=0";
        ds = em.createNativeQuery(s).getResultList();
        adminDiv_list = new ArrayList<_AdminDiv>(ds.size());
        id_adminDiv_map = new HashMap<Integer, _AdminDiv>(ds.size());
        code_adminDiv_map = new HashMap<Integer, _AdminDiv>(ds.size());
        for (Object[] d : ds) {
            _AdminDiv o = new _AdminDiv(((Number) d[0]).intValue(), Integer.parseInt(d[1].toString()), Integer.parseInt(d[2].toString()), ((Number) d[3]).intValue(),
                    (String) d[4], (String) d[5], (String) d[6]);
            adminDiv_list.add(o);
            id_adminDiv_map.put(o.ad_id, o);
            code_adminDiv_map.put(o.ad_code, o);
        }
        //</editor-fold>
        //<editor-fold desc="pos">
        s = "select ad_id, pos_code, pos_name, pos_addr, pos_kind_code, pos_sys_kind from md_pos where is_del=0 and pos_code<>'-1'";
        ds = em.createNativeQuery(s).getResultList();
        pos_list = new ArrayList<_Pos>(ds.size());
        for (Object[] d : ds) {
            _AdminDiv ad = id_adminDiv_map.get(((Number) d[0]).intValue());
            _Pos o = new _Pos(ad, (String) d[1], (String) d[2], (String) d[3], (String) d[4], (String) d[5]);
            pos_list.add(o);
        }
        //</editor-fold>
        //<editor-fold desc="existed channel sync">
        s = "select channel_code, id from dm_channel_sync where channel_code<>'-1'";
        ds = em.createNativeQuery(s).getResultList();
        code_id_channel_map = new HashMap<String, Long>(ds.size());
        for (Object[] d : ds) {
            code_id_channel_map.put((String) d[0], ((Number) d[1]).longValue());
        }
        //</editor-fold>
        channelSync_list = new ArrayList<_ChannelSync>(pos_list.size());
        for (_Pos pos : pos_list) {
            channelSync_list.add(_ChannelSync.newInstance(pos, code_id_channel_map));
        }
        int i = 0;
        for (_ChannelSync channelSync : channelSync_list) {
            try {
                em.createNativeQuery(channelSync.to_sql()).executeUpdate();
            } catch (Exception e) {
                logger.error("sync error {}", channelSync.to_sql());
                throw new RuntimeException(e);
            }
        }
    }

    private static class _AdminDiv {
        int ad_id;
        int ad_code;
        int ad_level;
        int deep;
        String ad_name;
        String city_ad_name;
        String province_ad_name;
        String province_ad_code;
        String city_ad_code;

        private _AdminDiv(int ad_id, int ad_code, int ad_level, int deep,
                          String ad_name, String city_ad_name, String province_ad_name) {
            int provinceCodePrefix = ad_code / 1000000;
            province_ad_code = String.valueOf(provinceCodePrefix * 1000000);
            city_ad_code = String.valueOf(ad_code / 10000 * 10000);
            if (null == city_ad_name) city_ad_name = ad_name;
            if (11 == provinceCodePrefix || 31 == provinceCodePrefix || 12 == provinceCodePrefix || 50 == provinceCodePrefix) {
                if (null == province_ad_code || null == province_ad_name) {
                    province_ad_code = String.valueOf(provinceCodePrefix * 1000000);
                    province_ad_name = ad_name;
                }
                city_ad_name = province_ad_name; // 直辖市
                city_ad_code = province_ad_code;
            }
            if (city_ad_name.length() > 3 && city_ad_name.endsWith("市辖区")) city_ad_name = city_ad_name.substring(0, city_ad_name.length() - 3);
            if (null == province_ad_name) province_ad_name = city_ad_name;
            if (null == ad_name || "市辖区".equals(ad_name) || "县".equals(ad_name)) ad_name = city_ad_name;
            // 非直辖市 city_ad_code == province_ad_code 关联错误修正
            if (11 != provinceCodePrefix && 31 != provinceCodePrefix && 12 != provinceCodePrefix && 50 != provinceCodePrefix) {
                if (city_ad_name.equals(province_ad_name)) {
                    city_ad_code = String.valueOf(ad_code / 10000 * 10000);
                    city_ad_name = ad_name;
                }
            }

            this.ad_id = ad_id;
            this.ad_code = ad_code;
            this.ad_level = ad_level;
            this.deep = deep;
            this.ad_name = ad_name;
            this.city_ad_name = city_ad_name;
            this.province_ad_name = province_ad_name;
        }
    }

    private static class _Pos {
        String pos_code;
        String pos_name;
        String pos_addr;
        String pos_kind_code;
        String pos_sys_kind;
        _AdminDiv adminDiv;

        private _Pos(_AdminDiv adminDiv, String pos_code, String pos_name, String pos_addr, String pos_kind_code, String pos_sys_kind) {
            this.pos_code = pos_code;
            this.pos_name = pos_name;
            this.pos_addr = pos_addr;
            this.pos_kind_code = pos_kind_code;
            this.pos_sys_kind = pos_sys_kind;
            this.adminDiv = adminDiv;
        }
    }

    private static class _ChannelSync {
        Long id;
        _AdminDiv adminDiv;
        String channel_code;
        String channel_cat_code;
        String channel_name;
        String channel_address;
        String channel_sys_cat;

        static _ChannelSync newInstance(_Pos pos, Map<String, Long> map) {
            _ChannelSync o = new _ChannelSync();
            o.adminDiv = pos.adminDiv;
            o.channel_code = pos.pos_code;
            o.channel_cat_code = pos.pos_kind_code;
            o.channel_name = pos.pos_name;
            o.channel_address = pos.pos_addr;
            o.channel_sys_cat = pos.pos_sys_kind;
            o.id = map.get(pos.pos_code);
            return o;
        }

        String to_sql() {
            StringBuilder sb = new StringBuilder(600);
            if (null == id) {
                sb.append("insert into dm_channel_sync(id,channel_ad_code,channel_ad_name,channel_address,channel_admin_div_id," +
                    "channel_cat_code,channel_city_ad_code,channel_city_ad_name,channel_code,channel_name,channel_province_ad_code," +
                    "channel_province_ad_name,channel_sys_cat,type) ");
                sb.append("values(hibernate_sequence.nextval");
                sb.append(",'").append(adminDiv.ad_code).append("'");
                sb.append(",'").append(adminDiv.ad_name).append("'");
                sb.append(",'").append(channel_address).append("'");
                sb.append(",").append(adminDiv.ad_id);

                sb.append(",'").append(channel_cat_code).append("'");
                sb.append(",'").append(adminDiv.city_ad_code).append("'");
                sb.append(",'").append(adminDiv.city_ad_name).append("'");
                sb.append(",'").append(channel_code).append("'");
                sb.append(",'").append(channel_name).append("'");
                sb.append(",'").append(adminDiv.province_ad_code).append("'");

                sb.append(",'").append(adminDiv.province_ad_name).append("'");
                sb.append(",'").append(channel_sys_cat).append("'");
                sb.append(",0)");
            } else {
                sb.append("update dm_channel_sync set ");
                sb.append("channel_ad_code='").append(adminDiv.ad_code).append("'");
                sb.append(",channel_ad_name='").append(adminDiv.ad_name).append("'");
                sb.append(",channel_address='").append(channel_address).append("'");
                sb.append(",channel_admin_div_id='").append(adminDiv.ad_id).append("'");

                sb.append(",channel_cat_code='").append(channel_cat_code).append("'");
                sb.append(",channel_city_ad_code='").append(adminDiv.city_ad_code).append("'");
                sb.append(",channel_city_ad_name='").append(adminDiv.city_ad_name).append("'");
                sb.append(",channel_code='").append(channel_code).append("'");
                sb.append(",channel_name='").append(channel_name).append("'");
                sb.append(",channel_province_ad_code='").append(adminDiv.province_ad_code).append("'");

                sb.append(",channel_province_ad_name='").append(adminDiv.province_ad_name).append("'");
                sb.append(",channel_sys_cat='").append(channel_sys_cat).append("'");

                sb.append(" where id=").append(id).append("");
            }
            return sb.toString();
        }
    }
}
